<?php

namespace App\Model;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class FinancePearlStream extends BaseModel
{
    protected $table = 'finance_pearl_stream';
    public $timestamps = false;


    public function getCabinWealthWater(Request $request, $room_id)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $dateType = $request->input('date_type', 'month');
        $dateTimeArray = $request->input('value');
        $where = $begin = $end = $format = '';
        if( isset($dateTimeArray[0]) && $dateTimeArray[0]){
            $begin = date('Y-m-d',strtotime($dateTimeArray[0]));
        }
        if(isset($dateTimeArray[1]) && $dateTimeArray[1]){
            $end = date('Y-m-d',strtotime($dateTimeArray[1]));
        }
        $format = '%Y-%m-%d';
        if ($begin && !$end) {
            $where .= " AND FROM_UNIXTIME(a.change_timestamp, '{$format}')>='$begin'";
        }

        if (!$begin && $end) {
            $where .= " AND FROM_UNIXTIME(a.change_timestamp, '{$format}')<='$end'";
        }
        if ($begin && $end) {
            $where .= " AND FROM_UNIXTIME(a.change_timestamp, '{$format}')>='$begin' AND FROM_UNIXTIME(a.change_timestamp, '{$format}')<='{$end}'";
        }
        $sql = "SELECT FROM_UNIXTIME(a.change_timestamp, '{$format}') as created_at,SUM(a.pearl_value)
            as room_wealth_value,a.room_id,b.room_uid,b.name,ANY_VALUE(a.pearl_type) pearl_type FROM zb_finance_pearl_stream
            AS a LEFT JOIN zb_room AS b ON a.room_id=b.room_id WHERE pearl_type IN ('0','1','2') AND a.room_id={$room_id} $where GROUP BY created_at,a.room_id";
        $data = DB::connection($this->connection)->select($sql);
        $total = count($data);
        $data = collect($data)->forPage($page, $limit)->values()->toArray();
        $count_value = 0;
        foreach ($data as $v) {
            $count_value += $v->room_wealth_value;
            $v->room_wealth_value = ltrim($v->room_wealth_value, '-');
        }
        if ($data) {
            $count['created_at'] = 'N/A';
            if ($begin && $end) $count['created_at'] = $begin . '至' . $end;
            $count['room_uid'] = '房间统计';
            $count['name'] = 'N/A';
            $count['room_wealth_value'] = ltrim($count_value, '-');
            $data[] = json_decode(json_encode($count));
        }
        return ['total' => $total, 'data' => $data];
    }


    public function getAllCabinWealthWater(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $room_uid = $request->input('room_uid')?? '';
        $where = ' WHERE business_type = 0 ';
        if ($room_uid) $where .= " AND room_uid = $room_uid";
        $date = date('Y-m-d');
        $upDay = date('Y-m-d', strtotime("$date -1 day"));
        $upWeek = date('Y-m-d', strtotime("$date -7 day"));
        $weekTime = getAWeekTimeSlotByYmd($date);//本周
        $upweekTime = getAWeekTimeSlotByYmd($upWeek);//上周
        $monthTime[] = date('Y-m-01', time());
        $monthTime[] = date('Y-m-t', time());
        $upMonthTime[] = date('Y-m-01', strtotime("$date -1 month"));
        $upMonthTime[] = date('Y-m-t', strtotime("$date -1 month"));

        $total = DB::connection('api')->selectOne("SELECT COUNT(*) total FROM zb_room {$where}");
        $now_page = ($page - 1) * $limit;
        $room_list = DB::connection('api')->select("SELECT room_id,room_uid,name,user_id,business_type FROM zb_room {$where} LIMIT {$now_page},{$limit}");
        $room_list = $this->objToArray($room_list);
        $room_id_arr = array_column($room_list, 'room_id');

        $room_id_str = implode(',', $room_id_arr);
        // 获取上月的数据
        $upMonthList = DB::connection('mysql')->select("SELECT room_id,convert(sum_pearl/200,decimal(12,2)) as upmonth_sum FROM cms_cabin_pearl_stream WHERE room_id IN({$room_id_str}) AND created_date >='$upMonthTime[0]' AND created_date <= '$upMonthTime[1]'");
        $upMonthList = $this->objToArray(array_column($upMonthList, null, 'room_id'));
        // 获取本月的数据

        $monthList = DB::connection('mysql')->select("SELECT room_id,convert(sum_pearl/200,decimal(12,2)) sum_wealth,created_date FROM cms_cabin_pearl_stream WHERE room_id IN({$room_id_str}) AND created_date >='$monthTime[0]' AND created_date <= '$monthTime[1]'");
        $monthSumArr = [];
        $monthList = $this->objToArray($monthList);

        // 本月、上周、本周
        foreach ($monthList as $key => $month) {
            if (isset($monthSumArr[$month['room_id']]['month_sum'])) {
                $monthSumArr[$month['room_id']]['month_sum'] += $month['sum_wealth'];
            } else {
                $monthSumArr[$month['room_id']]['month_sum'] = 0;
                $monthSumArr[$month['room_id']]['month_sum'] += $month['sum_wealth'];
            }
            // 上周
            if ($month['created_date'] >= $upweekTime[0] && $month['created_date'] <= $upweekTime[1]) {
                if (isset($monthSumArr[$month['room_id']]['upweek_sum'])) {
                    $monthSumArr[$month['room_id']]['upweek_sum'] += $month['sum_wealth'];
                } else {
                    $monthSumArr[$month['room_id']]['upweek_sum'] = 0;
                    $monthSumArr[$month['room_id']]['upweek_sum'] += $month['sum_wealth'];
                }
            }
            // 本周
            if ($month['created_date'] >= $weekTime[0] && $month['created_date'] <= $weekTime[1]) {
                if (isset($monthSumArr[$month['room_id']]['week_sum'])) {
                    $monthSumArr[$month['room_id']]['week_sum'] += $month['sum_wealth'];
                } else {
                    $monthSumArr[$month['room_id']]['week_sum'] = 0;
                    $monthSumArr[$month['room_id']]['week_sum'] += $month['sum_wealth'];
                }
            }
            // 昨日
            if ($month['created_date'] === $upDay) {
                if (isset($monthSumArr[$month['room_id']]['day_sum'])) {
                    $monthSumArr[$month['room_id']]['day_sum'] += $month['sum_wealth'];
                } else {
                    $monthSumArr[$month['room_id']]['day_sum'] = 0;
                    $monthSumArr[$month['room_id']]['day_sum'] += $month['sum_wealth'];
                }
            }
        }

        foreach($room_list as $k =>&$v){
            //上月
            $v['upMonthSum'] =  $v['monthSum'] = $v['upweekSum'] = $v['weekSum'] = $v['daySum'] =  0;
            if(isset($upMonthList[$v['room_id']])){
                $v['upMonthSum'] = $upMonthList[$v['room_id']]['upmonth_sum'];
            }
            if(isset($monthSumArr[$v['room_id']])){
                $monthSum = $monthSumArr[$v['room_id']]['month_sum'] ?? 0;
                $upweekSum = $monthSumArr[$v['room_id']]['upweek_sum'] ?? 0;
                $weekSum = $monthSumArr[$v['room_id']]['week_sum'] ?? 0;
                $daySum = $monthSumArr[$v['room_id']]['day_sum'] ?? 0;
                $v['monthSum'] = round($monthSum,2);
                $v['upweekSum'] = round($upweekSum,2);
                $v['weekSum'] = round($weekSum,2);
                $v['daySum'] = round($daySum,2);
            }
        }
        return ['total' => $total, 'data' => $room_list, 'currentPage' => intval($page)];
    }

    function getAWeekTimeSlot($gdate = '', $weekStart = 1)
    {
        if (!$gdate) {
            $gdate = date("Y-m-d");
        }
        $w = date("w", strtotime($gdate)); //取得一周的第几天,星期天开始0-6
        $dn = $w ? $w - $weekStart : 6; //要减去的天数
        $time[] = date("Y-m-d", strtotime("$gdate  - " . $dn . "  days "));
        $time[] = date("Y-m-d", strtotime("$time[0]  +6  days "));
        return $time; //返回开始和结束日期
    }
}
